/**
 * 
 */
package dao;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import model.DON_VI_TCM;
import model.MonHocM;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import util.DAL;
import util.MyConnectUnit;

public class MonHocD {
	private MyConnectUnit conn;

	/**
	 * @function constructor MonHocD
	 * @throws Exception
	 */
	public MonHocD() throws Exception {
		super();
		this.conn = DAL.getDAL();
	}

	/**
	 * @function get all MonHoc
	 * @param from
	 * @param to
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocM> getAlls(int id, int from, int to)
			throws Exception {
		String limit = from + ", " + to;
		ArrayList<MonHocM> ls = new ArrayList<MonHocM>();
		ResultSet rs = this.conn
				.Selects(
						"id_mon_hoc, ma, mh.ten, so_tc, so_tiet_lt, so_tiet_th, mh.id_don_vi_ql, dvtc.ten",
						"MON_HOC mh, DON_VI_TC dvtc",
						"mh.id_don_vi_ql = dvtc.id_don_vi_tc AND mh.id_don_vi_ql = "
								+ id, "ma ASC", limit);
		MonHocM l = null;
		while (rs.next()) {
			l = new MonHocM();
			l.setId_mon_hoc(rs.getInt("id_mon_hoc"));
			l.setMa(rs.getString("ma"));
			l.setTen(rs.getString("mh.ten"));
			l.setSo_tc(rs.getInt("so_tc"));
			l.setSo_tiet_lt(rs.getString("so_tiet_lt"));
			l.setSo_tiet_th(rs.getInt("so_tiet_th"));

			l.setId_don_vi_ql(rs.getInt("id_don_vi_ql"));
			DON_VI_TCM dvtc = new DON_VI_TCM();
			dvtc.setTen(rs.getString("dvtc.ten"));
			l.setTen_don_vi(dvtc);
			ls.add(l);
		}
		return ls;
	}
	
	/**
	 * @function get all MonHoc
	 * @param from
	 * @param to
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocM> getAlls(String ten, int from, int to)
			throws Exception {
		String limit = from + ", " + to;
		ArrayList<MonHocM> ls = new ArrayList<MonHocM>();
		ResultSet rs = this.conn
				.Selects(
						"id_mon_hoc, ma, mh.ten, so_tc, so_tiet_lt, so_tiet_th, mh.id_don_vi_ql, dvtc.ten",
						"MON_HOC mh, DON_VI_TC dvtc",
						"mh.id_don_vi_ql = dvtc.id_don_vi_tc AND mh.ten LIKE '%"
								+ ten + "%'", "ma ASC", limit);
		MonHocM l = null;
		while (rs.next()) {
			l = new MonHocM();
			l.setId_mon_hoc(rs.getInt("id_mon_hoc"));
			l.setMa(rs.getString("ma"));
			l.setTen(rs.getString("mh.ten"));
			l.setSo_tc(rs.getInt("so_tc"));
			l.setSo_tiet_lt(rs.getString("so_tiet_lt"));
			l.setSo_tiet_th(rs.getInt("so_tiet_th"));

			l.setId_don_vi_ql(rs.getInt("id_don_vi_ql"));
			DON_VI_TCM dvtc = new DON_VI_TCM();
			dvtc.setTen(rs.getString("dvtc.ten"));
			l.setTen_don_vi(dvtc);
			ls.add(l);
		}
		return ls;
	}

	
	/**
	 * @function get all MonHoc
	 * @param Condition
	 * @param Limit
	 * @param OrderBy
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocM> getAllLoad() throws Exception {
		ArrayList<MonHocM> ls = new ArrayList<MonHocM>();
		ResultSet rs = this.conn.Select("MON_HOC", null, null, "ten");
		MonHocM l = null;
		while (rs.next()) {
			l = new MonHocM();
			l.setId_mon_hoc(rs.getInt("id_mon_hoc"));
			l.setMa(rs.getString("ma"));
			l.setTen(rs.getString("ten"));
			l.setSo_tc(rs.getInt("so_tc"));
			l.setSo_tiet_lt(rs.getString("so_tiet_lt"));
			l.setSo_tiet_th(rs.getInt("so_tiet_th"));
			l.setId_don_vi_ql(rs.getInt("id_don_vi_ql"));
			ls.add(l);
		}
		return ls;
	}
	
	/**
	 * @function get all MonHoc
	 * @param Condition
	 * @param Limit
	 * @param OrderBy
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocM> getAllLoad1() throws Exception {
		ArrayList<MonHocM> ls = new ArrayList<MonHocM>();
		ResultSet rs = this.conn.Select("MON_HOC", null, null, "ma");
		MonHocM l = null;
		while (rs.next()) {
			l = new MonHocM();
			l.setId_mon_hoc(rs.getInt("id_mon_hoc"));
			l.setMa(rs.getString("ma"));
			l.setTen(rs.getString("ten"));
			l.setSo_tc(rs.getInt("so_tc"));
			l.setSo_tiet_lt(rs.getString("so_tiet_lt"));
			l.setSo_tiet_th(rs.getInt("so_tiet_th"));
			l.setId_don_vi_ql(rs.getInt("id_don_vi_ql"));
			ls.add(l);
		}
		return ls;
	}
	
	/**
	 * @function get all MonHoc
	 * @param Condition
	 * @param Limit
	 * @param OrderBy
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocM> getAll(String Condition, String Limit, String OrderBy) throws Exception {
		ArrayList<MonHocM> ls = new ArrayList<MonHocM>();
		ResultSet rs = this.conn.Select("MON_HOC", Condition, Limit, OrderBy);
		MonHocM l = null;
		while (rs.next()) {
			l = new MonHocM();
			l.setId_mon_hoc(rs.getInt("id_mon_hoc"));
			l.setMa(rs.getString("ma"));
			l.setTen(rs.getString("ten"));
			l.setSo_tc(rs.getInt("so_tc"));
			l.setSo_tiet_lt(rs.getString("so_tiet_lt"));
			l.setSo_tiet_th(rs.getInt("so_tiet_th"));
			l.setId_don_vi_ql(rs.getInt("id_don_vi_ql"));
			ls.add(l);
		}
		return ls;
	}

	/**
	 * @function get all MonHoc with Condition + Limit
	 * @param Condition
	 * @param Limit
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocM> getAll(String Condition, String Limit) throws Exception {
		return getAll(Condition, Limit, null);
	}

	/**
	 * @function get all MonHoc with Condition
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocM> getAll(String Condition) throws Exception {
		return getAll(Condition, null);
	}

	/**
	 * @function get all MonHoc no parameter
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocM> getAll() throws Exception {
		return getAll(null);
	}

	/**
	 * @function search ten MonHoc
	 * @param ten
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocM> searchMH(String ten) throws Exception {
		ArrayList<MonHocM> list = getAll("ten LIKE '%" + ten + "%'");
		return list;
	}

	/**
	 * @function get all MonHoc with search + paging
	 * @param from
	 * @param to
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocM> getLimit(String ten, int from, int to)
			throws Exception {
		String limit = from + ", " + to;
		ArrayList<MonHocM> list = getAll("ten LIKE '%" + ten + "%'", limit);
		return list;
	}
	
	/**
	 * @function test Ma Mon Hoc exits
	 * @param ma
	 * @return
	 * @throws Exception
	 */
	public int getMaMonHoc(String ma) throws Exception{
		ArrayList<MonHocM> l = getAll("ma = '" + ma + "'");
		return l.size();
	}
	
	
	/**
	 * @function get all MonHoc
	 * @param Condition
	 * @param Limit
	 * @param OrderBy
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocM> getAllLoad1(String condition, String sort) throws Exception {
		ArrayList<MonHocM> ls = new ArrayList<MonHocM>();
		ResultSet rs = this.conn.Select("MON_HOC", condition, null, sort);
		MonHocM l = null;
		while (rs.next()) {
			l = new MonHocM();
			l.setId_mon_hoc(rs.getInt("id_mon_hoc"));
			l.setMa(rs.getString("ma"));
			l.setTen(rs.getString("ten"));
			l.setSo_tc(rs.getInt("so_tc"));
			l.setSo_tiet_lt(rs.getString("so_tiet_lt"));
			l.setSo_tiet_th(rs.getInt("so_tiet_th"));
			l.setId_don_vi_ql(rs.getInt("id_don_vi_ql"));
			ls.add(l);
		}
		return ls;
	}
	/**
	 * @function get all MonHoc with Condition
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocM> loadMonHocDonVi(int  iddv) throws Exception {
		ArrayList<MonHocM> list = getAllLoad1("id_don_vi_ql = " + iddv, " ten" );
		return list;
	}
	
	/**
	 * @function get all MonHoc with Condition
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocM> loadMonHocDonVi1(int  iddv) throws Exception {
		ArrayList<MonHocM> list = getAllLoad1("id_don_vi_ql = " + iddv, " ma" );
		return list;
	}

	/**
	 * function insert MonHoc
	 * 
	 * @param mh
	 * @return
	 * @throws Exception
	 */
	public boolean Insert(MonHocM mh) throws Exception {
		HashMap<String, Object> map = new HashMap<String, Object>();
		map.put("ma", mh.getMa());
		map.put("ten", mh.getTen());
		map.put("so_tc", mh.getSo_tc());
		map.put("so_tiet_lt", mh.getSo_tiet_lt());
		map.put("so_tiet_th", mh.getSo_tiet_th());
		map.put("id_don_vi_ql", mh.getId_don_vi_ql());
		return this.conn.Insert("MON_HOC", map);
	}

	/**
	 * @function update MonHoc
	 * @param mh
	 * @return
	 * @throws Exception
	 */
	public boolean Update(MonHocM mh) throws Exception {
		HashMap<String, Object> map = new HashMap<String, Object>();
		map.put("ma", mh.getMa());
		map.put("ten", mh.getTen());
		map.put("so_tc", mh.getSo_tc());
		map.put("so_tiet_lt", mh.getSo_tiet_lt());
		map.put("so_tiet_th", mh.getSo_tiet_th());
		map.put("id_don_vi_ql", mh.getId_don_vi_ql());
		return this.conn.Update("MON_HOC", map,
				"id_mon_hoc = " + mh.getId_mon_hoc());
	}

	/**
	 * @function delete MonHoc
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public boolean Delete(int id) throws Exception {
		return this.conn.Delete("MON_HOC", "id_mon_hoc = " + id);
	}

	/**
	 * @function read excel data
	 * @param fileName
	 * @return
	 * @throws IOException
	 */
	public ArrayList<MonHocM> readExcel9703(String fileName) throws IOException {
		ArrayList<MonHocM> ls = new ArrayList<>();

		ArrayList<Object> cellDataList = new ArrayList<>();
		FileInputStream fileInputStream = null;
		try {
			fileInputStream = new FileInputStream(fileName);

			// get Workbook
			HSSFWorkbook workBook = new HSSFWorkbook(fileInputStream);

			// get Sheet
			HSSFSheet hssfSheet = workBook.getSheetAt(0);

			// get every row of Sheet
			Iterator<Row> rowIterator = hssfSheet.rowIterator();

			while (rowIterator.hasNext()) {
				HSSFRow hssfRow = (HSSFRow) rowIterator.next();

				// get every Cell of rows
				Iterator<Cell> iterator = hssfRow.cellIterator();

				List<HSSFCell> cellTempList = new ArrayList<HSSFCell>();
				while (iterator.hasNext()) {
					HSSFCell hssfCell = (HSSFCell) iterator.next();
					cellTempList.add(hssfCell);
				}
				cellDataList.add(cellTempList);
			}
			MonHocM l = null;
			for (int i = 1; i < cellDataList.size(); i++) {
				ArrayList<?> cellStoreArrayList = (ArrayList<?>) cellDataList.get(i);
				String ma = ((HSSFCell) cellStoreArrayList.get(1)).toString();
				String ten = ((HSSFCell) cellStoreArrayList.get(2)).toString();
				String so_tc = ((HSSFCell) cellStoreArrayList.get(3)).toString();
				String so_tiet_lt = ((HSSFCell) cellStoreArrayList.get(4)).toString();
				String so_tiet_th = ((HSSFCell) cellStoreArrayList.get(5)).toString();
				String ma_don_vi = ((HSSFCell) cellStoreArrayList.get(6)).toString();
				
				//System.out.println(ma + "  " + ten + "  " + so_tc + "   " + so_tiet_lt+"   "+so_tiet_th + "   "+ma_don_vi);
				l = new MonHocM();
				l.setMa(ma);
				l.setTen(ten);
				l.setSo_tc((int)Float.parseFloat(so_tc));
				l.setSo_tiet_lt((String.valueOf((int)Float.parseFloat(so_tiet_lt))));
				l.setSo_tiet_th((int)Float.parseFloat(so_tiet_th));
				l.setMa_don_vi(ma_don_vi);
				ls.add(l);
		}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (fileInputStream != null) {
				fileInputStream.close();
			}
		}
		return ls;
	}
}